package com.coderman.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Created by ${fanchunshuai} on 2017-6-28.
 *
 * @version: V1.0
 * @Desc:
 * @Copyright (c) 2017 58到家-版权所有
 */
public class CreateDatabase {
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/";

    //  Database credentials
    static final String USER = "root";
    static final String PASS = "root";

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try{
            //STEP 2: Register JDBC driver
            Class.forName("com.mysql.jdbc.Driver");

            //STEP 3: Open a connection
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            //STEP 4: Execute a query
            System.out.println("Creating database...");
            stmt = conn.createStatement();

            conn.setAutoCommit(false);
            String sql = "CREATE DATABASE db_java";

            //stmt.executeUpdate("DROP DATABASE db_java");
            //创建用户  java.sql.SQLException: Field 'ssl_cipher' doesn't have a default value
            //java.sql.SQLException: Field 'x509_issuer' doesn't have a default value
            stmt.executeUpdate("insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) values(\"localhost\",\"javacoder1\",\"java1234.\",'','','')");
            //刷新权限
            stmt.executeUpdate("FLUSH PRIVILEGES");
            //创建数据库
            stmt.executeUpdate(sql);
            //给改用户授权
            // Can't find any matching row in the user table
            stmt.executeUpdate("grant all privileges on db_java.* to javacoder1@localhost identified by 'java1234.' ");
            conn.commit();
            System.out.println("Database created successfully...");
        }catch(Exception e){
            try {
                System.out.println("rollback................................");
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            //Handle errors for Class.forName
            e.printStackTrace();
        }finally{
            //finally block used to close resources
            try{
                if(stmt!=null)
                    stmt.close();
            }catch(SQLException se2){
            }// nothing we can do
            try{
                if(conn!=null)
                    conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }//end finally try
        }//end try
        System.out.println("Goodbye!");
    }//end main
}
